﻿-- =============================================
-- Author:		Jon Stocksdale
-- Create date: April 25 2016
-- Description:	Revision of Taylor's ViewEntityRelationships_Changed to fetch a list of related entities based on a list
--              of PERMITTED relationships - The assumption being that whatever CALLS this Stored Procedure has verified
--              permissions from a RelationshipChain.
-- =============================================
CREATE PROCEDURE [dbo].[ReadRelatedEntities_old] 
	-- The entity whose permissions need to be returned
	@Entity1ID nvarchar(100),
	@Entity1Type nvarchar(255),
	@SRNameWithDir nvarchar(100) = null,
	@ReadDirect int=null,
	@ReadChild int=null,
	@WriteDirect int=null,
	@WriteChild int=null,
	@FilterStartDate datetime = null,
	@FilterEndDate datetime = null,
	@FilterTimeDiv nvarchar(100) = null
	-- The date for filtering any relationships that contain a start or end date
	-- Specifying only a Start Date returns all relationships that are valid ON THAT DATE!
	-- Specifying a Start Date AND End Date returns all relationships valid anytime between those two dates.
	-- TimeDiv can be specific TimeDiv ShortName or the word CURRENT.TimeDivType
	-- Example:  Current.SchoolYear or Current.Term
	-- When TimeDiv exists, StartDate and EndDate are ignored and the startdate and enddate of the TimeDiv is used
	-- If ALL filters are NULL, use today for StartDate
	-- Set TimeDiv to ALL to get all relationships ever.
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Get the XML list of valid relationships with Entity1Type and Permissions
	-- FOR THIS PURPOSE, DOUBLE CHECK THAT THE Relationship has at least basic direct read
	DECLARE RelationshipCursor cursor for
		SELECT * FROM GetEntityRelationships(@Entity1Type,@ReadChild, @WriteChild)
			WHERE ((@SRNameWithDir is null OR @SRNameWithDir = SRNameWithDir) AND (E1_Read_Direct_E2 > 0))
	
	If (@FilterTimeDiv is NOT null) BEGIN
	-- Lookup TimeDiv and set StartDate and EndDate appropriately.
		If SUBSTRING(@FilterTimeDiv,0,8) = 'CURRENT' BEGIN
			SELECT @FilterStartDate=StartDate, @FilterEndDate=EndDate FROM dbo.TimeDivisions WHERE StartDate < SYSDATETIME() AND EndDate > SYSDATETIME() AND TimeDivType = SUBSTRING(@FilterTimeDiv,9,100)
		END
		ELSE BEGIN
			SELECT @FilterStartDate=StartDate, @FilterEndDate=EndDate FROM dbo.TimeDivisions WHERE ShortName = @FilterTimeDiv
		END
	END
	PRINT @FilterStartDate
	
	-- If there was no Time Division specified or found, then the dates would not have been set.
	-- The following code should work either way
		IF (@FilterStartDate is null) BEGIN
		-- If there is no Start Date, assume Today.
			SET @FilterStartDate = SYSDATETIME()
		END
		IF (@FilterEndDate is null) BEGIN
		-- If there is no End Date, then assume a SINGLE date
			SET @FilterEndDate = @FilterStartDate
		END

	CREATE TABLE #EntityRelationships (
		Entity1ID nvarchar(100),
		Entity1Type nvarchar(100),
		Entity1Name nvarchar(255),
		SRNameWithDir nvarchar(100),
		RelationshipName nvarchar(50),
		SuperRelationship nvarchar(20),
		Entity2ID nvarchar(100), 
		Entity2Type nvarchar(100),
		Entity2Name nvarchar(255),
		StartDate datetime, 
		EndDate datetime,
		E1_ReadDirect_E2 int,
		E1_ReadChild_E2 int,
		E1_WriteDirect_E2 int,
		E1_WriteChild_E2 int
	)
	DECLARE @NEXTREL nvarchar(100)
	DECLARE @BuildSQL nvarchar(max)
	DECLARE @FriendlyID nvarchar(255)
	DECLARE @SuperRelationship nvarchar(12), @RD int, @RC int, @WD int, @WC int
	DECLARE @E1Type nvarchar(100), @E2Type nvarchar(100), @RelName nvarchar(50)

	-- Walk through the list of valid Relationships:
	Open RelationshipCursor
	Fetch NEXT FROM RelationshipCursor INTO  @NEXTREL, @RelName, @SuperRelationship, @E1Type, @E2Type, @RD, @RC, @WD, @WC
	WHILE @@FETCH_STATUS = 0 BEGIN
			SET @BuildSQL = 'SELECT Entity1ID, "' + @E1Type + '" AS Entity1Type, "" as Entity1Name, ' +
						' "' + @NEXTREL + '" as SRNameWithDir, RelationshipName, ' +
						' SuperRelationship, Entity2ID, "' + @E2Type + '" As Entity2Type, "" as Entity2Name,' +
						' StartDate, EndDate, ' + CAST(@RD AS NVARCHAR) + ', ' + CAST(@RC AS NVARCHAR) +
						 ', ' + CAST(@WD AS NVARCHAR) + ', ' + CAST(@WC AS NVARCHAR) + 
						' FROM [{' + @NEXTREL + ' R} WHERE (STARTDATE IS NULL OR ' +
						 ' STARTDATE < "' + CAST(@FilterEndDate AS NVARCHAR) +
						 '") AND (ENDDATE IS NULL OR ENDDATE > "' + CAST(@FilterStartDate AS NVARCHAR) + '") AND ' +
						 ' Entity1ID = "' + @Entity1ID + '" ] '

									 
				SET @BuildSQL = dbo.ConvertRafikiNOSQL(@BuildSQL,'','',@Entity1ID,'',@FilterStartDate,@FilterEndDate)



				IF LEFT(@BuildSQL,6) <> 'Error:' BEGIN
					EXECUTE('INSERT INTO #EntityRelationships(Entity1ID,Entity1Type,Entity1Name,
							SRNameWithDir, RelationshipName, SuperRelationship,	Entity2ID, 
							Entity2Type, Entity2Name, StartDate, EndDate, E1_ReadDirect_E2, E1_ReadChild_E2,
							E1_WriteDirect_E2, E1_WriteChild_E2) ' + @BuildSQL)
				END
			Fetch NEXT FROM RelationshipCursor INTO   @NEXTREL, @RelName, @SuperRelationship, @E1Type, @E2Type, @RD, @RC, @WD, @WC
	END
	CLOSE RelationshipCursor
	DEALLOCATE RelationshipCursor

	SELECT DISTINCT * FROM #EntityRelationships
END